﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace EmbeddedSensorCloud.Plugin.Temperature
{
    internal class TempDBManager
    {
        private readonly string _ConnectionString;
        private readonly string _TemperatureTable;

        #region Contructors

        /// <summary>
        /// Creates a new instance of TempDBManager with hardcoded values for 'server', 'database', 'uid' and 'password'.
        /// </summary>
        public TempDBManager()
        {
            this._TemperatureTable = "TemperatureData";
            this._ConnectionString = "SERVER = localhost; DATABASE = SWE_Temperature; UID = root; PASSWORD = myadminpw";
        }

        /// <summary>
        /// Creates a new instance of TempDBManager with the given parameters.
        /// </summary>
        /// <param name="server">The server name, URL or IP-Address of the server.</param>
        /// <param name="database">The database to perform the operations on.</param>
        /// <param name="tableName">The name of the table to perform the operations on.</param>
        /// <param name="uid">The username to use for the connection.</param>
        /// <param name="password">The password for the given user.</param>
        public TempDBManager(string server, string database, string tableName, string uid, string password)
        {
            this._TemperatureTable = tableName;
            this._ConnectionString = string.Format("SERVER = {0}; DATABASE = {1}; UID = {2}; PASSWORD = {3}", server, database, uid, password);
        }

        #endregion

        #region GetConnection

        /// <summary>
        /// Opens a new connection.
        /// </summary>
        /// <returns>The open connection object.</returns>
        private MySqlConnection GetConnection()
        {
            try
            {
                MySqlConnection connection = new MySqlConnection(_ConnectionString);
                connection.Open();
                return connection;
            }
            catch (MySqlException)
            {
                MySqlConnection.ClearAllPools();
                throw;
            }
        }

        #endregion

        #region InsertFunctions

        /// <summary>
        /// Inserts a given temperature value into the database.
        /// </summary>
        /// <param name="temperatureValue">The value to insert.</param>
        /// <returns>The number of rows affected by the INTSERT statement.</returns>
        public int InsertData(float temperatureValue)
        {
            using (MySqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("INSERT INTO `{0}`(`Temperature`) VALUES (@temperature);", this._TemperatureTable);
                MySqlCommand insertCommand = new MySqlCommand(sqlStatement, connection);
                insertCommand.Prepare();
                insertCommand.Parameters.AddWithValue("@temperature", temperatureValue);

                return insertCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Inserts a given list of temperature values into the database.
        /// </summary>
        /// <param name="temperatureValues">The list of values to insert.</param>
        /// <returns>The sum of rows affected by all INTSERT statements.</returns>
        public int InsertData(List<float> temperatureValues)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand insertCommand = new MySqlCommand();
                insertCommand.Connection = connection;
                insertCommand.CommandText = string.Format("INSERT INTO `{0}`(`Temperature`) VALUES (@temperature);", this._TemperatureTable);
                insertCommand.Prepare();
                insertCommand.Parameters.Add("@temperature", MySqlDbType.Float);
                //insertCommand.Parameters.AddRange(temperatureValues.ToArray());

                int affectedRows = 0;
                int affectedRowsSum = 0;

                foreach (float temperatureValue in temperatureValues)
                {
                    insertCommand.Parameters["temperature"].Value = temperatureValue;
                    affectedRows = insertCommand.ExecuteNonQuery();

                    if (affectedRows > 0)
                    { affectedRowsSum += affectedRows; }
                }
                return affectedRowsSum;
            }
        }

        #endregion

        #region GetFunctions

        /// <summary>
        /// Reads all temperature information of the given date.
        /// </summary>
        /// <param name="date">The date to look for.</param>
        /// <returns>A list that contains all found data.</returns>
        public List<TemperatureData> GetDataAtDate(DateTime date)
        {
            if (date == null)
            { throw new ArgumentNullException(); }

            using (MySqlConnection connection = GetConnection())
            {
                List<TemperatureData> dataList = new List<TemperatureData>();

                string sql = string.Format("SELECT `CaptureDate`, `Temperature` FROM `{0}` WHERE DATE(`CaptureDate`) = '{1}' ORDER BY `CaptureDate` DESC;", this._TemperatureTable, date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture));
                MySqlCommand command = new MySqlCommand(sql, connection);
                //command.Prepare();
                //command.Parameters.AddWithValue("@date", date);
                //command.Parameters.AddWithValue("@date", date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture));

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        TemperatureData newValue = new TemperatureData();
                        newValue.Date = reader.GetDateTime("CaptureDate");
                        newValue.Value = reader.GetFloat("Temperature");

                        dataList.Add(newValue);
                    }
                }
                return dataList;
            }
        }

        /// <summary>
        /// Reads all temperature information of the given date range. 
        /// </summary>
        /// <param name="fromDate">The begin date of the range.</param>
        /// <param name="toDate">The end date of the range.</param>
        /// <returns>A list that contains all found data.</returns>
        public List<TemperatureData> GetDataAtRange(DateTime fromDate, DateTime toDate)
        {
            if (fromDate == null || toDate == null)
            { throw new ArgumentNullException(); }

            using (MySqlConnection connection = GetConnection())
            {
                List<TemperatureData> dataList = new List<TemperatureData>();

                string sql = string.Format("SELECT `CaptureDate`, `Temperature` FROM `{0}` WHERE `CaptureDate` BETWEEN @fromDate AND @toDate ORDER BY `CaptureDate`;", this._TemperatureTable);
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.Prepare();
                command.Parameters.AddWithValue("@fromDate", fromDate);
                command.Parameters.AddWithValue("@toDate", toDate);

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        TemperatureData newValue = new TemperatureData();
                        newValue.Date = reader.GetDateTime("CaptureDate");
                        newValue.Value = reader.GetFloat("Temperature");

                        dataList.Add(newValue);
                    }
                }
                return dataList;
            }
        }

        /// <summary>
        /// Retrives all data of a given range starting at 'page' and counting 'limit' entries at maximum. (acpecting each 'page' has 'limit' entries)
        /// </summary>
        /// <param name="page">The page number to get.</param>
        /// <param name="limit">The number of entries each page has.</param>
        /// <returns>A list of all found data.</returns>
        public List<TemperatureData> GetDataOfPage(int page, int limit)
        {
            using (MySqlConnection connection = GetConnection())
            {
                List<TemperatureData> dataList = new List<TemperatureData>(limit);

                if (page > 0) { --page; }
                int startIndex = page * limit;

                string sql = string.Format("SELECT `CaptureDate`, `Temperature` FROM `{0}` ORDER BY `CaptureDate` DESC LIMIT @start, @count;", this._TemperatureTable);
                MySqlCommand command = new MySqlCommand(sql, connection);
                command.Prepare();
                command.Parameters.AddWithValue("@start", startIndex);
                command.Parameters.AddWithValue("@count", limit);

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        TemperatureData newValue = new TemperatureData();
                        newValue.Date = reader.GetDateTime("CaptureDate");
                        newValue.Value = reader.GetFloat("Temperature");

                        dataList.Add(newValue);
                    }
                }
                return dataList;
            }
        }

        /// <summary>
        /// Retrieves the number of data entries in the Temperature table.
        /// </summary>
        /// <returns>The count of entries.</returns>
        public int GetTempDataCount()
        {
            using (MySqlConnection connection = GetConnection())
            {
                string sql = string.Format("SELECT COUNT(ID) FROM {0};", this._TemperatureTable);
                MySqlCommand command = new MySqlCommand(sql, connection);

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    int numPages = -1;
                    while (reader.Read())
                    {
                        numPages = reader.GetInt32(0);
                    }
                    return numPages;
                }
            }
        }

        #endregion
    }
}
